Database Failure
I got back from a long weekend outdoor festival yesterday to discover that the city had suffered a massive storm, with numerous power outages which apparently caused a couple of the database files to become corrupt. If only my backup process hadn't stopped working six months ago...
My first clue that something was wrong was when I couldn't log into my web mail app ("login failed for user..."). I opened up SQL Server Management Studio and found that two databases (my client mail db and a not-important-at-all development db) were both throwing a "log scan number passed to log scan in database is not valid" error. A quick bit of googling learned me that this was pretty serious, and while there were some methods for possible fixing the error mostly that best thing to do was restore from backup. That's when I discovered that my most recent backups were dated "1/20/2013"!
The SQL Server backup process (which is a scheduled process on the server) was working fine. Once a night, every night, it would backup all of the databases into a Dropbox directory. The Dropbox app would then both upload those files to their central server and all of the other computers I have linked to that account. In this way not only are there several copies of the backup files on several computers (some off-site) there is also a versioned copy of each backup file in the central Dropbox system. The last bit is important because I can restore any backup file from with the back couple of weeks (I'm not sure how long the oldest version of any given file is - but I've never needed to go back father than what they provide by default).
The problem was that Dropbox doesn't make a version of their app that runs as a Windows service, they only make a desktop app. Which means that it only works while a user is logged into the machine. Well guess what? I'm not leaving a user logged into my server. So I'd set up a rickety system whereby I'd used srvany to run Dropbox as a service. This system occasionally just stops working. Apparently it just stopped working back in January and I hadn't until it was too late. (I wish Dropbox could be bothered to stop making APIs and adding extra languages for a couple weeks and build a damn Windows service version. I, and a about a hundred thousand other people, have been wanting this for years.)
So since restoring from backup was turning out to be an undesirable solution I went back to Google to see if their was something I could do to fix these corrupt files. After wasting a bunch of time reading forum after forum of people talking out of their ass I finally found an article worth two hills of beans: "EMERGENCY-mode repair: the very, very last resort" by Paul Randal at sqlskills.com. Here's what worked for me:
ALTER DATABASE [database name] SET EMERGENCY
DBCC CHECKDB (N'database name', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
There may be some lost records, and I'm pretty sure the transaction log is gone, but it works! Now I'm going to set about trying to get Dropbox working as a service again. What fun.